﻿package com.dbm.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.dbm.model.ConnPoolFactory;
import com.dbm.util.OwnerCodeUtil;
/**
 * ctl 与 wms 货主关系映射处理 Dao
 * @author wqying
 *
 */
public class OwnerCodeDao {
	Logger log = Logger.getLogger("ctl 与 wms 货主关系映射处理 Dao----com.gotop.dao.dbmdao.OwnerCodeDao");
	/**
	 * 根据 ID 获取 ownercode
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public synchronized OwnerCodeUtil getOwnerById(int id) throws Exception{
		OwnerCodeUtil ownerCode = new OwnerCodeUtil();
		Connection dbmConn = ConnPoolFactory.getDBMConnection();
		String sql = "select * from ownerCode where id=?";
		PreparedStatement stat = dbmConn.prepareStatement(sql);
		stat.setInt(1, id);
		ResultSet rs = stat.executeQuery();
		if(rs.next()){
			ownerCode.setID(rs.getInt("ID"));
			ownerCode.setCUSTOMERID(rs.getString("cUSTOMERID"));
			ownerCode.setOWNERCODE(rs.getString("ownerCode"));
			ownerCode.setWAREHOUSEID(rs.getString("warehouseId"));
			ownerCode.setWAREHOUSENAME(rs.getString("warehousename"));
		}
		rs.close();
		stat.close();
		dbmConn.close();
		return ownerCode;
	}
	
	/**
	 * 获取ownercode 列表
	 * @return List<OwnerCodeUtil>
	 * @throws Exception
	 */
	public synchronized List<OwnerCodeUtil> getAllOwnerCodeUtil() throws Exception{
		Connection dbmConn = ConnPoolFactory.getDBMConnection();
		List<OwnerCodeUtil> ownerUtils = new ArrayList<OwnerCodeUtil>();
		String sql = "SELECT * FROM ownercode";
		PreparedStatement stat = dbmConn.prepareStatement(sql);
		ResultSet rs = stat.executeQuery();
		while(rs.next()){
			OwnerCodeUtil ownerUtil = new OwnerCodeUtil();
			ownerUtil.setID(rs.getInt("ID"));
			ownerUtil.setCUSTOMERID(rs.getString("cUSTOMERID"));
			ownerUtil.setOWNERCODE(rs.getString("ownerCode"));
			ownerUtil.setWAREHOUSEID(rs.getString("warehouseId"));
			ownerUtil.setWAREHOUSENAME(rs.getString("warehousename"));
			ownerUtils.add(ownerUtil);
		}
		rs.close();
		stat.close();
		dbmConn.close();
		return ownerUtils;
	}
	/**
	 * 根据WMS系统中的 customerid 获取 CTL系统中的 ownerCode
	 * @param customerId
	 * @param dbmConn
	 * @return
	 * @throws Exception
	 */
	public synchronized String getOwnerCodeByCustomerId(String customerId)throws Exception{
		String ownerCode = "";
		Connection dbmConn = ConnPoolFactory.getDBMConnection();
		String sql = "SELECT o.ownerCode FROM ownerCode o where o.customerid=?";
		PreparedStatement stat = dbmConn.prepareStatement(sql);
		stat.setString(1,customerId);
		ResultSet rs = stat.executeQuery();
		if(rs.next()){
			ownerCode = rs.getString("ownerCode");
		}else{
			ownerCode = customerId;
		}
		log.info("erp 货主："+ownerCode+"\twms货主："+customerId);
		rs.close();
		stat.close();
		dbmConn.close();
		return ownerCode;
	}
	/**
	 * 根据ctl 的 ownercode 获取 wms 层的Customerid
	 * @param OwnerCode
	 * @param dbmConn
	 * @return
	 * @throws Exception
	 */
	public synchronized String getCustomerIdByOwnerCode(String ownerCode) throws Exception{
		String customerid = "";
		Connection dbmConn = ConnPoolFactory.getDBMConnection();
		String sql = "Select o.customerid FROM ownerCode o where o.ownerCode=?";
		PreparedStatement stat = dbmConn.prepareStatement(sql);
		stat.setString(1,ownerCode);
		ResultSet rs = stat.executeQuery();
		if(rs.next()){
			customerid = rs.getString("customerid");
		}else{
			customerid=ownerCode;
		}
		log.info("erp 货主："+ownerCode+"\twms货主："+customerid);
		rs.close();
		stat.close();
		dbmConn.close();
		return customerid;
	}
	/**
	 * 根据ID删除货主映射关系
	 * @param ownerCode
	 * @param dbmconn
	 * @return
	 * @throws Exception
	 */
	public synchronized boolean delOwnerCode(OwnerCodeUtil ownerCode)throws Exception{
		boolean flag = false;
		Connection dbmconn = ConnPoolFactory.getDBMConnection();
		String sql = "DELETE FROM ownerCode where id=?";
		PreparedStatement stat = dbmconn.prepareStatement(sql);
		stat.setInt(1, ownerCode.getID());
		if(stat.executeUpdate()==1)
			flag = true;
		stat.close();
		dbmconn.close();
		return flag;
	}
	/**
	 * 修改货主关系映射
	 * @param ownerCode
	 * @param dbmconn
	 * @return
	 * @throws Exception
	 */
	public boolean updateOwnerCode(OwnerCodeUtil ownerCode)throws Exception{
		boolean flag = false;
		Connection dbmconn = ConnPoolFactory.getDBMConnection();
		String sql = "UPDATE ownercode set ownercode=?,warehouseid=?,customerid=?,warehouseName=? where id=?";
		PreparedStatement stat = dbmconn.prepareStatement(sql);
		stat.setString(1, ownerCode.getOWNERCODE());
		stat.setString(2, ownerCode.getWAREHOUSEID());
		stat.setString(3, ownerCode.getCUSTOMERID());
		stat.setString(4, ownerCode.getWAREHOUSENAME());
		stat.setInt(5, ownerCode.getID());
		if(stat.executeUpdate()==1)
			flag = true;
		stat.close();
		dbmconn.close();
		return flag;
	}
	/**
	 * 新增货主映射关系
	 * @param ownerCode
	 * @param dbmconn
	 * @return
	 * @throws Exception
	 */
	public synchronized boolean addOwnerCode(OwnerCodeUtil ownerCode) throws Exception{
		boolean flag = false;
		Connection dbmconn = ConnPoolFactory.getDBMConnection();
		String sql = "Insert Into OwnerCode VALUES (seq_ownerCode.nextval,?,?,?,?)";
		PreparedStatement stat = dbmconn.prepareStatement(sql);
		stat.setString(1, ownerCode.getOWNERCODE());
		stat.setString(2, ownerCode.getCUSTOMERID());
		stat.setString(3, ownerCode.getWAREHOUSEID());
		stat.setString(4, ownerCode.getWAREHOUSENAME());
		if(stat.executeUpdate()==1)
			flag = true;
		stat.close();
		dbmconn.close();
		return flag;
	}
}
